package dao.daocontrollers;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import workerswithdb.IDAOGarage;
import api.entitites.IGarage;
import entitiesfactory.EntitiesFactory;

public class DAOGarage extends GenericDAO implements IDAOGarage {

	private Connection connection;

	private PreparedStatement freePlaces;
	private PreparedStatement countMasters;
	private PreparedStatement countGarages;

	private ResultSet garageFromDb_result;
	private ResultSet countFreePlaces_result;
	private ResultSet countMasters_result;
	private ResultSet countGarages_result;
	
	private static final String FREE_PLACES_ON_SERVICE = "select count(id_garage) from garages where fullness_garage=false";

	private static final String GET_COUNT_GARAGE = "select count(id_garage) from (select id_garage from garages where fullness_garage=false union select id_garage from orders where deadline_date<?)s";
	private static final String GET_COUNT_MASTER = "select count(id_master) from (select id_master from masters where employment_master=false union select id_master from orders where deadline_date<?)s";

	private ArrayList<String> answer = new ArrayList<String>();
	private EntitiesFactory entities_factory;

	public DAOGarage() {
		super(IDAOGarage.class);
		try {
			entities_factory=EntitiesFactory.getInstance();
			connection = super.getConnection();

			freePlaces = connection
					.prepareStatement(FREE_PLACES_ON_SERVICE);
			countGarages=connection.prepareStatement(GET_COUNT_GARAGE);
			countMasters=connection.prepareStatement(GET_COUNT_MASTER);

		} catch (SQLException sqle) {

		}
	}

	public ArrayList<String> insertGarageToDB(IGarage garage) {
		answer.clear();
		try {
			super.insert(garage.getNumberGarage(), garage.getEmptyGarage());
			answer.add(DBMessanger.INSERT_OPERATION_SUCCESS);

		} catch (SQLException sqle) {
			answer.clear();
			answer.add(DBMessanger.GARAGE_EXISTS);
		}
		return answer;
	}

	public IGarage getGarageFromDb(Integer number_garage) {
		try {
			IGarage garage =(IGarage) entities_factory.getObject(IGarage.class);
			garageFromDb_result = super.read(number_garage);
			while (garageFromDb_result.next()) {
				int number = garageFromDb_result.getInt(2);
				garage.setNumberGarage(number);
				boolean empty = garageFromDb_result.getBoolean(3);
				if (!empty) {
					garage.markGarageNotEmpty();
				}
			}
			return garage;
		} catch (SQLException sqle) {
			System.out.println("get exception");
			return null;
		}
	}

	public ArrayList<String> getEmptyGarage() {
		answer.clear();
		try {
			Integer countPlace = 0;
			countFreePlaces_result = freePlaces.executeQuery();
			while (countFreePlaces_result.next()) {
				countPlace = countFreePlaces_result.getInt(1);
			}

			if (countPlace == 0) {
				answer.add(DBMessanger.ALL_GARAGES_BUSY);
			} else {
				answer.add(DBMessanger.NUMBER_GARAGE + countPlace);
			}

		} catch (SQLException sqle) {
			answer.clear();
			answer.add(DBMessanger.ERROR);

		}
		return answer;

	}

	public ArrayList<String> getEmptyPlacesOnDate(Date date) {
		answer.clear();
		try {
			int numbersMaster = 0;
			int numberGarage = 0;
			countMasters.setDate(1, (java.sql.Date) date);
			countMasters_result = countMasters.executeQuery();
			while (countMasters_result.next()) {
				numbersMaster = countMasters_result.getInt(1);
			}
			countGarages.setDate(1, (java.sql.Date) date);
			countGarages_result = countGarages.executeQuery();
			while (countGarages_result.next()) {
				numberGarage = countGarages_result.getInt(1);
			}
			if (numberGarage == 0 || numbersMaster == 0) {
				answer.add(DBMessanger.NOT_EMPTY_PLACE_ON_DATE);

			} else if (numbersMaster > numberGarage) {
				answer.add(DBMessanger.NUMBER_GARAGE_ON_DATE + numberGarage);
			} else if (numbersMaster < numberGarage) {
				answer.add(DBMessanger.NUMBER_GARAGE_ON_DATE + numbersMaster);
			} else if (numbersMaster == numberGarage) {
				answer.add(DBMessanger.NUMBER_GARAGE_ON_DATE + numberGarage);
			}

		} catch (SQLException sqlE) {
			answer.clear();
			answer.add(DBMessanger.ERROR);

		}

		return answer;

	}

	public Integer getIdGarage(Integer number_garage) {
		try {
			Integer id_garage = super.getObjectId(number_garage);

			return id_garage;
		} catch (SQLException sqle) {
			System.out.println(DBMessanger.GARAGE_NOT_EXISTS);
			return null;
		}
	}

	public ArrayList<String> deleteGarage(Integer number_garage) {
		answer.clear();
		try {
			super.deleteObject(number_garage);
			answer.add(DBMessanger.GARAGE_DELETE);

		} catch (SQLException sqle) {
			answer.clear();
			answer.add(DBMessanger.ERROR);
		}

		return answer;
	}

}
